package dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import model.Dept;
import model.Duty;
import model.Education;
import model.Emp;
import model.Resume;
import util.DBUtil;
import util.Page;

public class ResumeDAOImpl implements dao.ResumeDAO {
	DBUtil dbu = new DBUtil();
	Connection conn = null;
	PreparedStatement stat = null;
	ResultSet rs = null;
	boolean result=false;
	SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd");
	
	public boolean addResume(Resume resume) {
		try {
			conn=dbu.getConnection();
			stat=conn.prepareStatement("insert into resume values(resumeSEQ.nextval,?,?,?,?,?,?,to_date(?,'YYYY-MM-DD'),to_date(?,'YYYY-MM-DD'))");
			stat.setInt(1, resume.getEmp().getId());
			stat.setInt(2, resume.getDept().getId());
			stat.setInt(3, resume.getDuty().getId());
			stat.setInt(4, resume.getEducation().getId());
			stat.setString(5, resume.getWorkcontent());
			stat.setDouble(6, resume.getScore());
			
			String date;
			date=sdf.format(resume.getStarttime());
			stat.setString(7, date);
			
			date=sdf.format(resume.getEndtime());
			stat.setString(8, date);
			
			if(stat.executeUpdate()>0){
				result=true;
			}
		} catch (Exception e) {
		}finally{
			dbu.closeConnection(conn, stat, rs);
		}
		
		return result;
	}

	public boolean deleteByEmp(Emp emp) {
		try {
			
		} catch (Exception e) {
		}
		return false;
	}

	public boolean deleteById(int id) {
		try {
			conn=dbu.getConnection();
			stat=conn.prepareStatement("delete from resume where id=?");
			stat.setInt(1, id);
			
			if (stat.executeUpdate()>0) {
				result=true;
			}
		} catch (Exception e) {
			
		}
		return result;
	}

	public Page<Resume> getByTime(int pageNo, int pageSize, Date starttime,
			Date endtime) {
		Page<Resume> page = new Page<Resume>(pageNo, pageSize);
		int totalCount = 0;
		List<Resume> list = new ArrayList<Resume>();
		try {
			conn = dbu.getConnection();
			String sql="select count(*) from resume";
			stat=conn.prepareStatement(sql);
			rs=stat.executeQuery();
			if (rs.next()) {
				totalCount = rs.getInt(1);
			}
			page.setTotalCount(totalCount);
			sql = "select * from (select d.*,rownum rn from (select * from Resume where starttime >to_date(?,'yyyy-mm-dd') and starttime<to_date(?,'yyyy-mm-dd')) d where rownum <=?) where rn >=?";
			stat = conn.prepareStatement(sql);
			
			stat.setString(1, sdf.format(starttime));
			stat.setString(2, sdf.format(endtime));
			stat.setInt(3, page.getPageNo()*pageSize);
			stat.setInt(4,(page.getPageNo()-1)*pageSize+1);
			rs = stat.executeQuery();
			while (rs.next()) {
				Resume resume=new Resume();
				resume.setId(rs.getInt(1));
				resume.setEmp(new EmpDAOImpl().getEmpById(rs.getInt(2)));
				resume.setDept(new DeptDAOImpl().getDeptById(rs.getInt(3)));
				resume.setDuty(new DutyDAOImpl().getDutyById(rs.getInt(4)));
				resume.setEducation(new EducationDAOImpl().getById(rs.getInt(5)));
				resume.setWorkcontent(rs.getString(6));
				resume.setScore(rs.getDouble(7));
				resume.setStarttime(rs.getDate(8));
				resume.setEndtime(rs.getDate(9));
				list.add(resume);
			}
			page.setList(list);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			dbu.closeConnection(conn, stat, rs);
		}
		return page;
	}

	public boolean updateResume(Resume resume) {
		try {
			conn = dbu.getConnection();
			String sql="update resume set empid=?,deptid=?,dutiesid=?,eduid=?,Workcontent=?,score=?,starttime=?,endtime=? where id=?";
			stat=conn.prepareStatement(sql);
			stat.setInt(1, resume.getEmp().getId());
			stat.setInt(2, resume.getDept().getId());
			stat.setInt(3, resume.getDuty().getId());
			stat.setInt(4, resume.getEducation().getId());
			stat.setString(5, resume.getWorkcontent());
			stat.setDouble(6, resume.getScore());
			stat.setDate(7, new java.sql.Date(resume.getStarttime().getTime()));
			stat.setDate(8, new java.sql.Date(resume.getEndtime().getTime()));
			stat.setInt(9, resume.getId());
			
			if(stat.executeUpdate()>0){
				result=true;
			}
			
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			dbu.closeConnection(conn, stat, rs);
		}
		return result;
	}

	public Page<Resume> getAllResume(int pageNo, int pageSize) {
		Page<Resume> page = new Page<Resume>(pageNo, pageSize);
		int totalCount = 0;
		List<Resume> list = new ArrayList<Resume>();
		try {
			conn = dbu.getConnection();
			String sql="select count(*) from resume";
			stat=conn.prepareStatement(sql);
			rs=stat.executeQuery();
			if (rs.next()) {
				totalCount = rs.getInt(1);
			}
			page.setTotalCount(totalCount);
			sql = "select * from (select d.*,rownum rn from (select * from Resume) d where rownum <=?) where rn >=?";
			stat = conn.prepareStatement(sql);
			
			stat.setInt(1, page.getPageNo()*pageSize);
			stat.setInt(2,(page.getPageNo()-1)*pageSize+1);
			rs = stat.executeQuery();
			while (rs.next()) {
				Resume resume=new Resume();
				resume.setId(rs.getInt(1));
				resume.setEmp(new EmpDAOImpl().getEmpById(rs.getInt(2)));
				resume.setDept(new DeptDAOImpl().getDeptById(rs.getInt(3)));
				resume.setDuty(new DutyDAOImpl().getDutyById(rs.getInt(4)));
				resume.setEducation(new EducationDAOImpl().getById(rs.getInt(5)));
				resume.setWorkcontent(rs.getString(6));
				resume.setScore(rs.getDouble(7));
				resume.setStarttime(rs.getDate(8));
				resume.setEndtime(rs.getDate(9));
				list.add(resume);
			}
			page.setList(list);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			dbu.closeConnection(conn, stat, rs);
		}
		return page;
	}

	public Page<Resume> getByType(int pageNo, int pageSize, Object object) {
		Page<Resume> page = new Page<Resume>(pageNo, pageSize);
		int totalCount = 0;
		List<Resume> list = new ArrayList<Resume>();
		try {
			if (object.getClass().getName()== "model.Dept") {
				conn = dbu.getConnection();
				String sql="select count(*) from resume where deptid=?";
				stat=conn.prepareStatement(sql);
				stat.setInt(1, ((Dept) object).getId());
				rs=stat.executeQuery();
				if (rs.next()) {
					totalCount = rs.getInt(1);
				}
				page.setTotalCount(totalCount);
				sql = "select * from (select d.*,rownum rn from (select * from Resume where deptid=?) d where rownum <=?) where rn >=?";
				stat = conn.prepareStatement(sql);
				stat.setInt(1, ((Dept) object).getId());
				stat.setInt(2, page.getPageNo()*pageSize);
				stat.setInt(3,(page.getPageNo()-1)*pageSize+1);
			}else if (object.getClass().getName()=="model.Emp") {
				conn = dbu.getConnection();
				String sql="select count(*) from Resume where empid=?";
				stat=conn.prepareStatement(sql);
				stat.setInt(1, ((Emp) object).getId());
				rs=stat.executeQuery();
				if (rs.next()) {
					totalCount = rs.getInt(1);
				}
				page.setTotalCount(totalCount);
				sql = "select * from (select d.*,rownum rn from (select * from Resume where empid=?) d where rownum <=?) where rn >=?";
				stat = conn.prepareStatement(sql);
				stat.setInt(1, ((Emp) object).getId());
				stat.setInt(2, page.getPageNo()*pageSize);
				stat.setInt(3,(page.getPageNo()-1)*pageSize+1);
			}else if (object.getClass().getName()=="model.Education") {
				conn = dbu.getConnection();
				String sql="select count(*) from resume  where eduid=?";
				stat=conn.prepareStatement(sql);
				stat.setInt(1, ((Education) object).getId());
				rs=stat.executeQuery();
				if (rs.next()) {
					totalCount = rs.getInt(1);
				}

				page.setTotalCount(totalCount);
				sql = "select * from (select d.*,rownum rn from (select * from Resume where eduid=?) d where rownum <=?) where rn >=?";
				stat = conn.prepareStatement(sql);
				stat.setInt(1, ((Education) object).getId());
				stat.setInt(2, page.getPageNo()*pageSize);
				stat.setInt(3,(page.getPageNo()-1)*pageSize+1);
			}else if (object.getClass().getName()=="model.Duty") {
				conn = dbu.getConnection();
				String sql="select count(*) from resume where dutiesid=?";
				stat=conn.prepareStatement(sql);
				stat.setInt(1, ((Duty) object).getId());
				rs=stat.executeQuery();
				if (rs.next()) {
					totalCount = rs.getInt(1);
				}

				page.setTotalCount(totalCount);
				sql = "select * from (select d.*,rownum rn from (select * from Resume where dutiesid=?) d where rownum <=?) where rn >=?";
				stat = conn.prepareStatement(sql);
				stat.setInt(1, ((Duty) object).getId());
				stat.setInt(2, page.getPageNo()*pageSize);
				stat.setInt(3,(page.getPageNo()-1)*pageSize+1);
			}
			
			rs = stat.executeQuery();
			while (rs.next()) {
				Resume resume=new Resume();
				resume.setId(rs.getInt(1));
				resume.setEmp(new EmpDAOImpl().getEmpById(rs.getInt(2)));
				resume.setDept(new DeptDAOImpl().getDeptById(rs.getInt(3)));
				resume.setDuty(new DutyDAOImpl().getDutyById(rs.getInt(4)));
				resume.setEducation(new EducationDAOImpl().getById(rs.getInt(5)));
				resume.setWorkcontent(rs.getString(6));
				resume.setScore(rs.getDouble(7));
				resume.setStarttime(rs.getDate(8));
				resume.setEndtime(rs.getDate(9));
				list.add(resume);
			}
			page.setList(list);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			dbu.closeConnection(conn, stat, rs);
		}
		return page;
	}

}
